package com.exonline.front.controller.courses;

import com.exonline.commons.base.IBaseService;
import com.exonline.commons.dao.DaoImpl;
import com.exonline.commons.dao.IDao;
import com.exonline.commons.entity.Courses;
import com.exonline.commons.entity.Direction;
import com.exonline.commons.entity.Subject;
import org.apache.commons.dbutils.handlers.*;

import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @desc 课程模块的M层
 * @author hbrj
 */
public class CoursesService implements IBaseService<Courses> {

    // 引入数据访问对象
    IDao<Courses> coursesIDao = new DaoImpl<>();

    /**
     * @desc  1. 个人中心学习记录的查询
     * @author hbrj
     * @throws SQLException
     */
    public List<Map<String, Object>> getIndividualCourses(String studentId) throws SQLException {
        String sql = "SELECT c.* FROM studentcourses sc JOIN student s ON sc.sid = s.id JOIN courses c ON sc.cid = c.id WHERE s.id =? ";
        return coursesIDao.queryWithPagingAndConditions(sql, null, 1, 10, new MapListHandler(), studentId);
    }

    /**
     * @desc 添加收藏课程
     */
    public boolean addCourseToCollection(String sid, String cid, String id) {
        if (sid == null || cid == null) {
            // 如果学生ID或者课程ID为null，直接返回添加失败
            return false;
        }
        // 可以在这里添加更多对studentId和courseId格式等合法性的校验逻辑，比如是否符合特定的ID格式要求等
        try {
            // 先查询是否已经存在相同的收藏记录
            String checkSql = "SELECT COUNT(*) FROM collect WHERE sid =? AND cid =?";
            Long count = coursesIDao.queryForObject(checkSql, new ScalarHandler<Long>(), sid, cid);
            if (count != null && count > 0) {
                // 如果已经存在收藏记录，根据业务需求决定返回值，这里假设返回false表示不重复添加
                return false;
            }

            // 构建插入收藏记录的SQL语句
            String sql = "INSERT INTO collect (id,sid, cid) VALUES (?,?,?)";
            // 使用coursesIDao执行插入操作，传入学生ID和课程ID作为参数
            int affectedRows = coursesIDao.updateWithTransaction(sql, id, sid, cid);
            // 如果插入操作影响的行数大于0，表示插入成功，返回true；否则返回false
            return affectedRows > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    /**
     * @desc  2. 教师端课程信息的查询
     * @author hbrj
     * @throws SQLException
     */
    public List<Map<String, Object>> tqueryById(String tid) throws SQLException {
        // 查询语句
        String sql = "SELECT t.id AS id," + "c.cover AS course_cover," + "c.id AS course_id," + "c.cname AS course_name," +
                "c.cprice AS course_price," + "c.cviews AS course_views," + "c.csales AS course_sales," + "c.cstatus AS course_status" +
                "FROM teacher t\n" +
                "JOIN userrole ur ON t.id = ur.tid\n" +
                "JOIN courses c ON ur.tid = c.tid\n" +
                "WHERE t.id =?";
        return coursesIDao.queryWithPagingAndConditions(sql, null, 1, 10, new MapListHandler(), tid);
    }

    /**
     * @desc 3. 根据指定的榜单类型进行相应榜单数据查询
     * @author hbrj
     * @param boardType 榜单类型，取值可以为 "best_selling"（畅销榜）或 "most_studied"（学习榜），用于区分要查询的具体榜单
     * @return 对应榜单课程信息列表（以Map形式表示每条课程记录）
     * @throws SQLException SQL执行异常
     */
    public List<Map<String, Object>> getBoardData(String boardType) throws SQLException {
        // 1. 根据传入的榜单类型进行不同的查询
        // 1.1 畅销榜
        if ("best_selling".equals(boardType)) {
            String sql = "SELECT \n" +
                    "    c.id AS course_id,\n" +
                    "    c.cname AS course_name,\n" +
                    "    c.cprice AS course_price,\n" +
                    "    c.csales AS sales_count,\n" +
                    "    c.cintroduce AS courses_introduce,\n" +
                    "    c.clevel AS course_level,\n" +
                    "    t.tname AS teacher_name\n" +
                    "FROM courses c\n" +
                    "JOIN teacher t ON (c.tid = t.id)\n" +
                    "ORDER BY c.csales DESC";
            return coursesIDao.queryWithPagingAndConditions(sql, null, 1, 10, new MapListHandler());
            // 1.2 学习榜
        } else if ("most_studied".equals(boardType)) {
            String sql = "SELECT c.id AS course_id,\n" +
                    "       c.cname AS course_name,\n" +
                    "       c.cprice AS course_price,\n" +
                    "       c.cintroduce AS courses_introduce,\n" +
                    "       c.clevel AS course_level,\n" +
                    "       COUNT(sc.sid) AS student_count\n" +
                    "FROM courses c\n" +
                    "LEFT JOIN studentcourses sc ON c.id = sc.cid\n" +
                    "LEFT JOIN userorder uo ON c.id = uo.cid AND sc.sid = uo.sid\n" +
                    "GROUP BY c.id, c.cname, c.cintroduce, c.clevel\n" +
                    "ORDER BY student_count DESC";
            return coursesIDao.queryWithPagingAndConditions(sql, null, 1, 10, new MapListHandler());
        }
        return null;
    }


    /**
     * 4. 获取新课推荐的课程信息列表（以Map形式表示每条课程记录），
     * 当前按照查询最近一周内上传的课程来实现，可根据实际需求调整查询逻辑。
     * @author hbrj
     * @return 新课推荐课程信息列表
     * @throws SQLException SQL执行异常
     */
    public List<Direction> getNewCourses() throws SQLException {
        List<Direction> directions = coursesIDao.queryWithPagingAndConditions("SELECT DISTINCT cdirection AS directionName FROM newcourseview", null, 1, 8, new BeanListHandler<>(Direction.class));
        List<Subject> subjects = coursesIDao.queryWithPagingAndConditions("SELECT DISTINCT csubject AS subjectName FROM newcourseview", null, 1, 8, new BeanListHandler<>(Subject.class));
        List<Courses> courses = coursesIDao.queryWithPagingAndConditions("SELECT * FROM newcourseview", null, 1, 8, new BeanListHandler<>(Courses.class));
        //以方向为单位,返回课程信息
        for (Direction direction : directions) {
            String directionName = direction.getDirectionName();


            for (Courses cours : courses) {
                String courseSubjectName = cours.getCsubject();
                String courseDirectionName = cours.getCdirection();

                for (Subject subject : subjects) {

                    if (courseSubjectName.equals(subject.getSubjectName()) && !subject.getCourses().contains(cours)) {
                        subject.addCourses(cours);
                    }
                }
                if (courseDirectionName.equals(directionName)) {
                    for (Subject subject : subjects) {

                        if (courseSubjectName.equals(subject.getSubjectName()) && !direction.getSubjects().contains(subject)) {
                            direction.addSubjects(subject);
                        }
                    }
                }
            }
        }

        return directions;
    }


    /**
     * @desc  5. 获取收藏课程列表
     * @author hbrj
     * @param sid
     */
    public List<Map<String, Object>> getCollectedCourses(String sid) {
        try {
            // 1. 构建查询SQL语句，通过collect表与courses表进行关联查询，获取收藏课程的相关信息
            String sql = "SELECT c.cname,\n" +
                    "       c.cintroduce,\n" +
                    "       c.cprice,\n" +
                    "       c.cover,\n" +
                    "       sc.id\n" +
                    "FROM collect sc\n" +
                    "JOIN courses c ON sc.cid = c.id\n" +
                    "WHERE sc.sid =?";
            // 2. 使用coursesIDao执行查询操作，并返回结果（以Map形式表示每条课程记录）
            return coursesIDao.queryWithPagingAndConditions(sql, null, 1, 10, new MapListHandler(), sid);
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }


    /**
     * @desc  6. 添加收藏记录到收藏表中
     * @author hbrj
     */
    public boolean addCollectRecord(String collectId, String sid, String cid) throws SQLException {
        String sql = "INSERT INTO collect (id, sid, cid) VALUES (?,?,?)";
        // 执行插入操作，并返回受影响的行数
        int rowsAffected = coursesIDao.updateWithTransaction(sql, collectId, sid, cid);
        return rowsAffected > 0;
    }

    /**
     * @desc  7. 查询全部 课程信息
     * @author hbrj
     */
    @Override
    public List<Courses> queryAll(int pageSize, int pageNo) throws SQLException {
        // 添加cstatus = '1'的筛选条件，确保只查询到状态为1的课程
        List<Courses> courses = coursesIDao.queryWithPagingAndConditions("SELECT * FROM courses WHERE cstatus = '1'", null, pageNo, pageSize, new BeanListHandler<>(Courses.class));
        return courses;
    }

    /**
     * @desc  8. 根据条件查询全部课程信息  全部条件查询课程  ( 免费的 和  付费的 )
     * @param condition 条件
     * @author hbrj
     */
    public List<Courses> queryAll(String condition, int pageSize, int pageNo) throws SQLException {
        // 1. 设置查询条件
        String sqlCondition = null;
            // 1.1 免费的
        if ("free".equals(condition)) {
            sqlCondition = "cprice like " + "'0%'" + " AND cstatus = '1'";
            // 1.2 付费的
        } else if ("paid".equals(condition)) {
            sqlCondition = "cstatus = '1' AND cprice not like" + "'0%'";
        }
        // 2. 根据不同条件添加cstatus = '1'的筛选条件，确保查询结果符合要求
        List<Courses> courses = coursesIDao.queryWithPagingAndConditions("SELECT * FROM courses WHERE cstatus = '1'", sqlCondition, pageNo, pageSize, new BeanListHandler<>(Courses.class));
        return courses;
    }


    /**
     * @desc  9. 根据id查询课程信息 查询一条
     * @author hbrj
     */
    @Override
    public Courses queryOne(String id) throws SQLException {
        // 1. 添加cstatus = '1'的筛选条件，确保只查询到状态为1的课程
        Courses courses = coursesIDao.queryForObject("SELECT c.*,t.tname as tname, (select count(*) from coursesresource where cid = c.id) AS coursescount\n" +
                                                          "FROM courses c JOIN teacher t ON t.id = c.tid\n" +
                                                           "WHERE c.id =?" + "c.id = ?", new BeanHandler<Courses>(Courses.class), id);
        // 2. 查询课程的资源信息，并封装到Map中
        List<String> urls = coursesIDao.queryForObject("SELECT * FROM coursesresource WHERE cid = ?", new ColumnListHandler<>("videourl"), id);
        // 3. 遍历资源列表，获取每个资源的标题和链接，
        Map<String, Object> map = new HashMap<>();
        // 4. 将每个资源的标题和链接添加到Map中
        for (String url : urls) {
            // 4.1 获取每个资源的标题
            String title = url.substring(url.lastIndexOf("-") - 2, url.lastIndexOf("."));
            // 4.2 如果标题不存在于Map中，则添加该标题和对应的链接
            if (!map.containsKey(title)) {
                map.put(title, url);
            }
        }
        System.out.println(map);
        System.out.println(urls);
        courses.setVideourls(map);
        return courses;
    }

    /**
     * @desc  10. 课程的添加
     * @author hbrj
     */

    public void add(Object... obj) throws SQLException {
        // 更新插入语句，添加cover字段
        coursesIDao.updateWithTransaction("INSERT INTO courses (id, tid, cover,csales, clevel, cname, cintroduce, cprice, cdirection, csubject, cviews, cstatus) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)", obj);
    }


    /**
     * @desc  11. 课程的更新  id作为条件
     * @author hbrj
     */
    public void update(Object... obj) throws SQLException {
        coursesIDao.updateWithTransaction("INSERT INTO courses VALUES(?,?,?,?,?,?,?,?,?) WHERE id=?", obj);
    }


    /**
     * @desc 12. 课程根据id进行删除
     * @author hbrj
     */
    public void deleteById(String id) throws SQLException {
        // 1. 删除课程信息
        coursesIDao.updateWithTransaction("DELETE FROM courses WHERE id=?", id);
    }

    /**
     * @desc  13. 结构话查询 (分层: 方法  科目  课程)
     * @author hbrj
     */
    public List<Direction> structurequery(String condition, int pageSize, int pageNo) throws SQLException {
        //1. 获取查询条件
        String sqlCondition = null;
        if ("free".equals(condition)) {
            sqlCondition = "cprice like " + "'0%'";
        } else if ("paid".equals(condition)) {
            sqlCondition = "cprice not like" + "'0%'";
        } else if ("tfree".equals(condition)) {
            sqlCondition = "cprice like " + "'0%'" + " AND cstatus = " + "'1'";
        } else if ("tpaid".equals(condition)) {
            sqlCondition = "cprice not like" + "'0%'" + " AND cstatus = " + "'1'";
        } else if ("ffree".equals(condition)) {
            sqlCondition = "cprice  like" + "'0%'" + " AND cstatus = " + "'0'";
        } else if ("fpaid".equals(condition)) {
            sqlCondition = "cprice  not like" + "'0%'" + " AND cstatus =" + "'0'";
        }
        //2.获取方向、学科和课程信息
        List<Direction> directions = coursesIDao.queryWithPagingAndConditions("SELECT DISTINCT cdirection AS directionName FROM courses", null, 1, 100, new BeanListHandler<>(Direction.class));
        List<Subject> subjects = coursesIDao.queryWithPagingAndConditions("SELECT DISTINCT csubject AS subjectName FROM courses", null, 1, 100, new BeanListHandler<>(Subject.class));
        List<Courses> courses = coursesIDao.queryWithPagingAndConditions("SELECT c.*,t.tname FROM courses c join teacher t on t.id = c.tid", sqlCondition, pageNo, 10000, new BeanListHandler<>(Courses.class));
        System.out.println(courses);
        // 3.以方向为单位，返回课程信息
        for (Direction direction : directions) {
            String directionName = direction.getDirectionName();
            // 3.1 遍历课程列表
            for (Courses cours : courses) {
                String courseSubjectName = cours.getCsubject();
                String courseDirectionName = cours.getCdirection();

                for (Subject subject : subjects) {
                    // 3.2如果学科匹配，添加课程
                    if (courseSubjectName.equals(subject.getSubjectName()) && !subject.getCourses().contains(cours)) {
                        subject.addCourses(cours);
                    }
                }
                   // 3.3如果方向匹配，添加学科
                if (courseDirectionName.equals(directionName)) {
                    for (Subject subject : subjects) {
                        if (courseSubjectName.equals(subject.getSubjectName()) && !direction.getSubjects().contains(subject)) {
                            direction.addSubjects(subject);
                        }
                    }
                }
            }
        }
        return directions;
    }

    /**
     * @desc  14. 模糊查询课程信息
     * @author hbrj
     */
    public List<Courses> searchCoursesByName(String searchValue, int queryType) {
        try {
            // 1 根据查询类型选择不同的SQL语句
            String sql = "";
            // 2 根据查询类型执行不同的查询操作 (queryType  为1 课程名称的模糊查询  为2 课程科目的模糊查询)
            if (queryType == 1) {
                // 2.1 按课程名模糊查询的SQL语句
                sql = "SELECT * FROM courses WHERE (cname IS NOT NULL AND cname LIKE?)";
            } else if (queryType == 2) {
                // 2.2 按科目模糊查询的SQL语句
                sql = "SELECT * FROM courses WHERE (csubject IS NOT NULL AND csubject LIKE?)";
            }
            // 使用coursesIDao执行查询操作，传入相应参数（已添加通配符），并将结果封装为Courses对象列表返回
            return coursesIDao.queryWithPagingAndConditions(sql, null, 1, 10000, new BeanListHandler<>(Courses.class), "%" + searchValue + "%");
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }
}
